﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using WorkFlowSystem.Common;

namespace WorkFlowSystem.DataBase
{
    class DBOperator
    {
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <returns></returns>
        public int ConnectDB()
        {
            int result = 0;
            ConfigOperator config = new ConfigOperator();
            string data_src = "";
            string conn_str = "";

            if (config.GetStrValue("appSettings", "data_source", ref data_src) < 0)
            {
                return -1;
            }
            if ("".Equals(data_src))
            {
                return -2;
            }

            //conn_str = "Data Source=" + data_src + ";Initial Catalog=erp_database;Integrated Security=True";
            conn_str = "Server=" + data_src + "\\SQLEXPRESS;database=erp_database;user ID=test;password=sherry_2014";

            if (m_conn == null)
                m_conn = new SqlConnection(conn_str);
            try
            {
                if (m_conn.State == System.Data.ConnectionState.Open)
                    m_conn.Close();

                m_conn.Open();
            }
            catch (Exception ex)
            {
                m_conn.Close();
                Logger.WriteLog("connection open failed[" + ex.Message + "]");
                result = -1;
            }
            return result;
        }

        /// <summary>
        /// 打开数据库连接(设置用)
        /// </summary>
        /// <returns></returns>
        public int ConnectDB(string ip)
        {
            int result = 0;
            
            string conn_str = "";
                   
            conn_str = "Server=" + ip +"\\SQLEXPRESS;database=erp_database;user ID=test;password=sherry_2014";

            if (m_conn == null)
                m_conn = new SqlConnection(conn_str);
            try
            {
                if (m_conn.State == System.Data.ConnectionState.Open)
                    m_conn.Close();

                m_conn.Open();
            }
            catch (Exception ex)
            {
                m_conn.Close();
                Logger.WriteLog("connection open failed[" + ex.Message + "]");
                result = -1;
            }
            return result;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <returns></returns>
        public int CloseDB()
        {
            if (m_conn.State != System.Data.ConnectionState.Closed)
                m_conn.Close();
            return 0;
        }

        /// <summary>
        /// 执行sql返回影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="is_transaction"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, List<SqlParameter> para_list, bool transaction)
        {
            Logger.WriteSqlLog(sql, para_list);
            int result = 0;
            if (m_conn.State != System.Data.ConnectionState.Open)
            {
                Logger.WriteLog("connection is not open");
                return -1;
            }

            SqlCommand sql_com = null;
            if (para_list == null)
            {
                sql_com = new SqlCommand(sql, m_conn);
            }
            else
            {
                sql_com = get_sqlcom(sql, para_list);
            }

            if (!transaction)
            {
                try
                {
                    result = sql_com.ExecuteNonQuery();
                    sql_com.Parameters.Clear();
                }
                catch (Exception ex)
                {
                    Logger.WriteLog("excute sql[" + sql + "] failed ***********************, [" + ex.Message + "]");
                    result = -1;
                }
            }
            else
            {
                SqlTransaction sql_tran = m_conn.BeginTransaction();
                sql_com.Transaction = sql_tran;
                try
                {
                    Logger.WriteLog("begin transaction[" + sql + "]");
                    result = sql_com.ExecuteNonQuery();
                    sql_com.Parameters.Clear();
                    sql_tran.Commit();
                }
                catch (Exception ex)
                {
                    Logger.WriteLog("commit transaction failed[" + ex.Message + "], begin rollback");
                    try
                    {
                        sql_tran.Rollback();
                    }
                    catch (Exception ex1)
                    {
                        Logger.WriteLog("rollback transaction failed[" + ex1.Message + "]");
                    }
                    result = -1;
                }
            }
            return result;
        }

        /// <summary>
        /// 执行sql返回首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="is_transaction"></param>
        /// <returns></returns>
        public int ExecuteScalar(string sql, List<SqlParameter> para_list, bool transaction, ref object first_cell)
        {
            Logger.WriteSqlLog(sql, para_list);
            int result = 0;
            if (m_conn.State != System.Data.ConnectionState.Open)
            {
                Logger.WriteLog("connection is not open");
                return -1;
            }

            SqlCommand sql_com = null;
            if (para_list == null)
            {
                sql_com = new SqlCommand(sql, m_conn);
            }
            else
            {
                sql_com = get_sqlcom(sql, para_list);
            }

            if (!transaction)
            {
                try
                {
                    first_cell = sql_com.ExecuteScalar();
                    sql_com.Parameters.Clear();
                }
                catch (Exception ex)
                {
                    Logger.WriteLog("excute sql[" + sql + "] failed, [" + ex.Message + "]");
                    result = -1;
                }
            }
            else
            {
                SqlTransaction sql_tran = m_conn.BeginTransaction();
                sql_com.Transaction = sql_tran;
                try
                {
                    Logger.WriteLog("begin transaction[" + sql + "]");
                    first_cell = sql_com.ExecuteScalar();
                    sql_com.Parameters.Clear();
                    sql_tran.Commit();
                }
                catch (Exception ex)
                {
                    Logger.WriteLog("commit transaction failed[" + ex.Message + "], begin rollback");
                    try
                    {
                        sql_tran.Rollback();
                    }
                    catch (Exception ex1)
                    {
                        Logger.WriteLog("rollback transaction failed[" + ex1.Message + "]");
                    }
                    result = -1;
                }
            }
            return result;
        }

        /// <summary>
        /// 执行sql返回DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="transaction"></param>
        /// <param name="dt"></param>
        /// <returns>DataTable中成功添加或刷新的行数</returns>
        public int ExecuteDataTable(string sql, List<SqlParameter> para_list, bool transaction, ref DataTable dt)
        {
            Logger.WriteSqlLog(sql, para_list);
            int result = 0;
            if (m_conn.State != System.Data.ConnectionState.Open)
            {
                Logger.WriteLog("connection is not open");
                return -1;
            }

            SqlCommand sql_com = null;
            if (para_list == null)
            {
                sql_com = new SqlCommand(sql, m_conn);
            }
            else
            {
                sql_com = get_sqlcom(sql, para_list);
            }

            SqlDataAdapter sql_adptr = new SqlDataAdapter(sql_com);
            if (!transaction)
            {
                try
                {
                    result = sql_adptr.Fill(dt);
                    sql_com.Parameters.Clear();
                }
                catch (Exception ex)
                {
                    Logger.WriteLog("excute sql[" + sql + "] failed, [" + ex.Message + "]");
                    result = -1;
                }
            }
            else
            {
                SqlTransaction sql_tran = m_conn.BeginTransaction();
                sql_com.Transaction = sql_tran;
                try
                {
                    Logger.WriteLog("begin transaction[" + sql + "]");
                    result = sql_adptr.Fill(dt);
                    sql_com.Parameters.Clear();
                    sql_tran.Commit();
                }
                catch (Exception ex)
                {
                    Logger.WriteLog("commit transaction failed[" + ex.Message + "], begin rollback");
                    try
                    {
                        sql_tran.Rollback();
                    }
                    catch (Exception ex1)
                    {
                        Logger.WriteLog("rollback transaction failed[" + ex1.Message + "]");
                    }
                    result = -1;
                }
            }
            return result;
        }

        /// <summary>
        /// 执行sql返回DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="transaction"></param>
        /// <param name="ds"></param>
        /// <returns>DataTable中成功添加或刷新的行数</returns>
        public int ExecuteDataSet(string sql, List<SqlParameter> para_list, bool transaction, ref DataSet ds)
        {
            Logger.WriteSqlLog(sql, para_list);
            int result = 0;
            if (m_conn.State != System.Data.ConnectionState.Open)
            {
                Logger.WriteLog("connection is not open");
                return -1;
            }

            SqlCommand sql_com = null;
            if (para_list == null)
            {
                sql_com = new SqlCommand(sql, m_conn);
            }
            else
            {
                sql_com = get_sqlcom(sql, para_list);
            }

            SqlDataAdapter sql_adptr = new SqlDataAdapter(sql_com);
            if (!transaction)
            {
                try
                {
                    result = sql_adptr.Fill(ds);
                    sql_com.Parameters.Clear();
                }
                catch (Exception ex)
                {
                    Logger.WriteLog("excute sql[" + sql + "] failed, [" + ex.Message + "]");
                    result = -1;
                }
            }
            else
            {
                SqlTransaction sql_tran = m_conn.BeginTransaction();
                sql_com.Transaction = sql_tran;
                try
                {
                    Logger.WriteLog("begin transaction[" + sql + "]");
                    result = sql_adptr.Fill(ds);
                    sql_com.Parameters.Clear();
                    sql_tran.Commit();
                }
                catch (Exception ex)
                {
                    Logger.WriteLog("commit transaction failed[" + ex.Message + "], begin rollback");
                    try
                    {
                        sql_tran.Rollback();
                    }
                    catch (Exception ex1)
                    {
                        Logger.WriteLog("rollback transaction failed[" + ex1.Message + "]");
                    }
                    result = -1;
                }
            }
            return result;
        }

        /// <summary>
        /// 执行sql返回DataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="data_reader"></param>
        /// <returns></returns>
        public int ExecuteDataReader(string sql, List<SqlParameter> para_list, bool transaction, ref SqlDataReader dr)
        {
            Logger.WriteSqlLog(sql, para_list);
            int result = 0;
            if (m_conn.State != System.Data.ConnectionState.Open)
            {
                Logger.WriteLog("connection is not open");
                return -1;
            }

            SqlCommand sql_com = null;
            if (para_list == null)
            {
                sql_com = new SqlCommand(sql, m_conn);
            }
            else
            {
                sql_com = get_sqlcom(sql, para_list);
            }

            if (!transaction)
            {
                try
                {
                    dr = sql_com.ExecuteReader();
                    sql_com.Parameters.Clear();
                }
                catch (Exception ex)
                {
                    Logger.WriteLog("excute sql[" + sql + "] failed, [" + ex.Message + "]");
                    result = -1;
                }
            }
            else
            {
                SqlTransaction sql_tran = m_conn.BeginTransaction();
                sql_com.Transaction = sql_tran;
                try
                {
                    Logger.WriteLog("begin transaction[" + sql + "]");
                    dr = sql_com.ExecuteReader();
                    sql_com.Parameters.Clear();
                    sql_tran.Commit();
                }
                catch (Exception ex)
                {
                    Logger.WriteLog("commit transaction failed[" + ex.Message + "], begin rollback");
                    try
                    {
                        sql_tran.Rollback();
                    }
                    catch (Exception ex1)
                    {
                        Logger.WriteLog("rollback transaction failed[" + ex1.Message + "]");
                    }
                    result = -1;
                }
            }
            return result;
        }

        /// <summary>
        /// 装配sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="para_list"></param>
        /// <returns></returns>
        private SqlCommand get_sqlcom(string sql, List<SqlParameter> para_list)
        {
            SqlCommand com = new SqlCommand(sql, m_conn);
            for (int i = 0; i < para_list.Count; i++)
            {
                com.Parameters.Add(para_list[i]);
            }
            return com;
        }

        private SqlConnection m_conn;
    }
}
